import io
import os
import time
import zipfile

import pandas as pd
from tqdm import tqdm

book1 = None
book2 = None
book3 = None

# hexiao = 'excel_ar/核销.xlsx'
# douyin = 'excel_ar/抖音.xlsx'
# quanmingxi = 'excel_ar/券明细.xlsx'
# bucanjia = 'excel_ar/不参加.xlsx'

excelName1 = '返款表.xlsx'
excelName2 = '过程表.xlsx'
excelName3 = '供应商承担费用明细.xlsx'
excelName4 = '供应商总计.xlsx'


def startExcel(hexiao, douyin, quanmingxi, bucanjia):
    print("<<<<<<<<<<<<<<<安然报表计算开始>>>>>>>>>>>>>>>>")
    # progress_bar = tqdm(total=100, desc=f"正在执行")
    # time.sleep(0.5)
    # progress_bar.update(10)
    yield 10, None
    # 读取表格1
    # gongyingshang = pd.read_excel(gongyingshang, dtype=str)  # 替换为您的表格1文件路径

    coupon_data = pd.read_excel(hexiao, dtype={'订单编号': str, '券码': str, '商品ID': str})  # 替换为您的表格1文件路径
    # progress_bar.update(20)
    yield 30, None
    # 读取表格2
    sales_data = pd.read_excel(douyin, dtype={'抖音订单编号': str, '卡券编码': str, '商品编码': str})  # 替换为您的表格2文件路径
    # progress_bar.update(20)
    yield 50, None

    # 读取表格3
    coupon_details = pd.read_excel(quanmingxi, dtype=str)  # 替换为您的表格1文件路径
    coupon_details = coupon_details[['商品ID', '商品编码']]
    exclude_data = pd.read_excel(bucanjia, dtype={'商品编码': str})  # 替换为您的表格3文件路径
    # progress_bar.update(20)
    yield 70, None
    newtable = coupon_data[['订单编号', '商品名称', '金度科技', '供货商承担金额', '门店承担金额', '费用', '商品原价', '券码', '商品ID']].drop_duplicates()
    # progress_bar.update(10)
    yield 80, None
    # 合并表格2和表格1
    merged_data = pd.merge(sales_data, newtable, left_on='卡券编码', right_on='券码', how='left')
    # progress_bar.update(10)
    yield 90, None
    # 商品编码为空的行  只能时优惠券
    filtered_coupon_table = coupon_details[pd.isna(coupon_details['商品编码'])].copy()

    # 判断找出所有可核销单品
    hebing = pd.merge(merged_data, coupon_details, on=['商品ID', '商品编码'], how='left',
                      indicator='sp2').drop_duplicates()
    # 判断找出所有优惠券商品
    hebing = pd.merge(hebing, filtered_coupon_table, on='商品ID', how='left', indicator='sp1').drop_duplicates()
    # 判断找出所有不能核销的品类
    hebing = pd.merge(hebing, exclude_data, left_on='商品编码_x', right_on='商品编码', how='left',
                      indicator='_merge_1').drop_duplicates()
    hebing = hebing.astype({'商品ID': str, '商品编码': str})

    hebing['是否参加'] = 0
    hebing.loc[
        ((hebing['sp2'] == 'both') | (hebing['sp1'] == 'both')) & (hebing['_merge_1'] == 'left_only'), '是否参加'] = 1

    sales_ratio = hebing['销售金额'] / hebing.groupby('券码')['销售金额'].transform('sum')
    # hebing.to_excel('过程.xlsx',index=False)
    duochengdan = (sales_ratio * hebing['供货商承担金额'] + sales_ratio * hebing['金度科技']) * (1 - hebing['是否参加'])

    hebing['供货商实际承担费用'] = sales_ratio * hebing['供货商承担金额'] * hebing['是否参加']
    hebing['金度科技实际承担费用'] = sales_ratio * hebing['金度科技'] * hebing['是否参加']
    hebing['门店实际承担费用'] = (sales_ratio * hebing['门店承担金额']) + duochengdan

    hebing = hebing.astype({'供货商实际承担费用': float, '金度科技实际承担费用': float, '门店实际承担费用': float, '券码': str, '商品ID': str})

    hebing.to_excel(f'excel_ar/out/{excelName2}', index=False)
    # global book1
    # book1 = hebing
    # book1 = hebing.to_excel(guochengstream, index=False)
    print(f'表格【{excelName2}】计算完毕')

    def outputFankuan(table):
        table = table.groupby('卡券编码').agg({
            '供货商实际承担费用': 'sum',
            '金度科技实际承担费用': 'sum',
            '门店实际承担费用': 'sum'
        }).reset_index()
        # table = table[['卡券编码', '供货商实际承担费用', '金度科技实际承担费用', '门店实际承担费用']]
        # 合并
        table = pd.merge(table, coupon_data, left_on='卡券编码', right_on='券码', how='right').drop_duplicates()
        table = table.groupby('店号').agg({
            '核销门店': 'first',
            '商品原价': 'sum',
            '费用': 'sum',
            '券售卖金额': 'sum',
            '供货商实际承担费用': 'sum',
            '金度科技实际承担费用': 'sum',
            '门店实际承担费用': 'sum',
            '软件服务费': 'sum',
            '达人佣金': 'sum',
            '服务商佣金': 'sum',
            '商家应得': 'sum'
        }).reset_index()
        table.to_excel(f'excel_ar/out/{excelName1}', index=False)
        # global book2
        # book2 = table.to_excel(fankuanstream, index=False)
        # book2 = table
        print(f'表格【{excelName1}】计算完毕')

    def outputGongyingshang(table):
        # 按照券ID和商品编码分组
        shangping = table.groupby(['商品ID', '商品编码_x']).agg({
            '商品名称_x': 'first',
            '分类编码': 'first',
            '分类名称': 'first',
            '销售金额': 'sum',
            '供货商实际承担费用': 'sum',
            '金度科技实际承担费用': 'sum'
        }).reset_index()

        # 排序
        # shangping = shangping.sort_values(by='商品ID')

        # 按照券ID和商品编码分组
        fini1 = pd.DataFrame(shangping)
        # 这个符号就是同时的意思 &  我知道了
        fini1 = fini1[(fini1['供货商实际承担费用'] != 0) | (fini1['金度科技实际承担费用'] != 0)]
        # global book3
        # book3 = fini1
        fini1.to_excel(f'excel_ar/out/{excelName3}', index=False)
        # fini1.to_excel(fini1stream, index=False)
        # fini1stream.seek(0)
        # fis = fini1stream.read()
        # fini1stream.close()
        # fini2 = pd.merge(left=gongyingshang, right=fini1, left_on='商品编码', right_on='商品编码_x')
        # f1 = fini2.groupby('供货商编码').agg({'供货商实际承担费用': 'sum','供货商名称':'first','销售金额':'sum'}).reset_index()

        # f2 = pd.DataFrame(f1)
        # f2.to_excel(f'excel_ar/out/{excelName4}', index=False)
        # print(f'表格【{excelName3}】计算完毕')

    outputFankuan(hebing)
    # progress_bar.update(5)
    yield 95, None
    outputGongyingshang(hebing)

    # progress_bar.update(5)
    # return zip_buffer

    # with zipfile.ZipFile(zip_buffer, 'w') as zipf:
    zipname = f'{time.strftime("%Y%m%d%H%M%S", time.localtime())}_arzip.zip'
    with zipfile.ZipFile(f'excel_ar/out/{zipname}', 'w') as zipf:
        # for i, df in enumerate([book1, book2, book3], start=1):
        #     excel_buffer = io.BytesIO()
        #     df.to_excel(excel_buffer, index=False)
        #     print(excel_buffer.getbuffer().nbytes)
        #     excel_buffer.seek(0)
        #     zipf.writestr(f'data_{i}.xlsx', excel_buffer.read())
        #     # excel_buffer.close()
        zipf.write(f'excel_ar/out/{excelName1}', f'{excelName1}')
        zipf.write(f'excel_ar/out/{excelName2}', f'{excelName2}')
        zipf.write(f'excel_ar/out/{excelName3}', f'{excelName3}')
    # progress_bar.close()
    yield 100, f'baobiao/download?fileName={zipname}'
    pathlist = [f'excel_ar/out/{excelName1}', f'excel_ar/out/{excelName2}', f'excel_ar/out/{excelName3}']
    [os.remove(x) for x in pathlist]

# startExcel(hexiao,douyin,quanmingxi,bucanjia)
